package org.dynamo.database.reverse.config.reader.internal;
/*******************************************************************************
 * This program and the accompanying materials
 * are made available under the terms of the Eclipse Public License v1.0
 * which accompanies this distribution, and is available at
 * http://www.eclipse.org/legal/epl-v10.html
 *
 * Contributors:
 *     Jean Neveux - initial API and implementation
 *******************************************************************************/
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.dynamo.database.reverse.emf.metadata.Column;
import org.dynamo.database.reverse.emf.metadata.ForeignKey;
import org.dynamo.database.reverse.emf.metadata.Index;
import org.dynamo.database.reverse.emf.metadata.PrimaryKey;
import org.dynamo.database.reverse.emf.metadata.Table;
import org.dynamo.database.reverse.emf.metadata.UniqueKey;

public class MetadataReader {

	private static final Log log = LogFactory.getLog(MetadataReader.class);

	private final Connection connection;
	private final SQLExceptionConverter sec;
	private final ReverseEngineeringStrategy revengStrategy;

	private DatabaseMetaData metaData;

	public MetadataReader(Connection connection, SQLExceptionConverter sec,
			ReverseEngineeringStrategy reveng) {
		this.connection = connection;
		this.sec = sec;
		this.revengStrategy = reveng;
	}

	public DatabaseCollector readDatabaseSchema(DatabaseCollector dbs,
			String catalog, String schema, ProgressListener progress)
			throws SQLException, JDBCBinderException {

		processTables(dbs, catalog, schema, progress);

		Iterator<Table> tables = dbs.iterateTables();
		while (tables.hasNext()) {
			Table table = tables.next();
			processBasicColumns(table, progress);
			processPrimaryKey(table);
			processIndices(table);
		}

		tables = dbs.iterateTables();
		Map<String, List<ForeignKey>> oneToManyCandidates = new HashMap<String, List<ForeignKey>>(1);
		while (tables.hasNext()) {
			Table table = tables.next();
			Map<String, List<ForeignKey>> foreignKeys = processForeignKeys(dbs, table, progress);
			mergeMultiMap(oneToManyCandidates, foreignKeys);
		}

		dbs.setOneToManyCandidates(oneToManyCandidates);

		return dbs;
	}

	protected Map<String, List<ForeignKey>> processForeignKeys(DatabaseCollector dbs,
			Table referencedTable, ProgressListener progress)
			throws JDBCBinderException, SQLException {

		Map<String, List<Column>> dependentColumns = new HashMap<String, List<Column>>(
				10);

		Map<String, Table> dependentTables = new HashMap<String, Table>(10);
		Map<String, List<Column>> referencedColumns = new HashMap<String, List<Column>>(10);

		short bogusFkName = 0;

		ResultSet exportedKeyRs = null;
		log.debug("Calling getExportedKeys on " + referencedTable);
		progress.startSubTask("Finding exported foreignkeys on "
				+ referencedTable.getName());
		try {
			exportedKeyRs = getMetaData().getExportedKeys(
					referencedTable.getCatalog(), referencedTable.getSchema(),
					referencedTable.getName());
			try {
				while (exportedKeyRs.next()) {
					String fkCatalog = exportedKeyRs.getString("FKTABLE_CAT");
					String fkSchema = exportedKeyRs.getString("FKTABLE_SCHEM");
					String fkTableName = exportedKeyRs
							.getString("FKTABLE_NAME");
					String fkColumnName = exportedKeyRs
							.getString("FKCOLUMN_NAME");
					String pkColumnName = exportedKeyRs
							.getString("PKCOLUMN_NAME");
					String fkName = exportedKeyRs.getString("FK_NAME");
					short keySeq = exportedKeyRs.getShort("KEY_SEQ");

					log.debug("foreign key name: " + fkName);

					Table fkTable = dbs.getTable(fkSchema, fkCatalog,
							fkTableName);
					if (fkTable == null) {
						log.debug("Foreign key " + fkName
								+ " references unknown or filtered table "
								+ fkTableName);
						continue;
					}

					if (keySeq == 0) {
						bogusFkName++;
					}

					if (fkName == null) {
						fkName = Short.toString(bogusFkName);
					}

					List<Column> depColumns = dependentColumns
							.get(fkName);
					
					if (depColumns == null) {
						depColumns = new ArrayList<Column>(10);
						dependentColumns.put(fkName, depColumns);
						dependentTables.put(fkName, fkTable);
					} else {
						Object previousTable = dependentTables.get(fkName);
						if (fkTable != previousTable) {
							log.warn("Foreign key name ("
											+ fkName
											+ ") mapped to different tables! previous: "
											+ previousTable + " current:"
											+ fkTable);
						}
					}

					List<Column> refColumns = referencedColumns.get(fkName);
					if(refColumns == null)
					{
						refColumns = new ArrayList<Column>(10);
						referencedColumns.put(fkName, refColumns);
					}

					Column column = DatabaseCollector.getFactory().createColumn();
					column.setName(fkColumnName);
					Column existingColumn =	DatabaseCollector.getColumnByName(fkTable, column); 
					column = existingColumn == null ? column : existingColumn;

					Column refColumn = DatabaseCollector.getFactory().createColumn();
					refColumn.setName(pkColumnName);
					Column existingRefColumn = DatabaseCollector.getColumnByName(referencedTable, refColumn); 
					refColumn = existingRefColumn == null ? refColumn : existingRefColumn;

					refColumns.add(refColumn);
					depColumns.add(column);
				}
			} finally {
				try {
					exportedKeyRs.close();
				} catch (SQLException se) {
					log.warn("Exception while closing result set for foreign key meta data",
									se);
				}
			}
		} catch (SQLException se) {
			throw sec.convert(se,
					"Exception while reading foreign key meta data", null);
		}
		List<ForeignKey> userForeignKeys = revengStrategy.getForeignKeys();
		if (userForeignKeys != null) {
			Iterator<ForeignKey> iterator = userForeignKeys.iterator();
			while (iterator.hasNext()) {
				ForeignKey element = iterator.next();

				if (!equalTable(referencedTable, element.getReferencedTable())) {
					log.debug("Referenced table "
							+ element.getReferencedTable().getName()
							+ " is not " + referencedTable
							+ ". Ignoring userdefined foreign key " + element);
					continue; // skip non related foreign keys
				}

				String userfkName = element.getName();
				Table userfkTable = element.getTable();

				List<Column> userColumns = element.getColumns();
				List<Column> userrefColumns = element.getReferencedColumns();

				Table deptable = dependentTables.get(userfkName);
				if (deptable != null) { 
					throw new JDBCBinderException("Foreign key " + userfkName
							+ " already defined in the database!");
				}

				deptable = dbs.getTable(userfkTable.getSchema(), userfkTable
						.getCatalog(), userfkTable.getName());
				if (deptable == null) {
					log.debug("User defined foreign key " + userfkName
							+ " references unknown or filtered table "
							+ TableIdentifier.create(userfkTable));
					continue;
				}

				dependentTables.put(userfkName, deptable);

				List<Column> depColumns = new ArrayList<Column>(userColumns.size());
				Iterator<Column> colIterator = userColumns.iterator();
				while (colIterator.hasNext()) {
					Column jdbcColumn = colIterator.next();
					Column column = DatabaseCollector.getFactory().createColumn();
					column.setName(jdbcColumn.getName());
					Column existingColumn = DatabaseCollector.getColumnByName(deptable,column); 
					column = existingColumn == null ? column : existingColumn;
					depColumns.add(column);
				}

				List<Column> refColumns = new ArrayList<Column>(userrefColumns.size());
				colIterator = userrefColumns.iterator();
				while (colIterator.hasNext()) {
					Column jdbcColumn = colIterator.next();
					Column column = DatabaseCollector.getFactory().createColumn();
					column.setName(jdbcColumn.getName());
					Column existingColumn = DatabaseCollector.getColumnByName(referencedTable, column); 
					column = existingColumn == null ? column : existingColumn;
					refColumns.add(column);
				}

				referencedColumns.put(userfkName, refColumns);
				dependentColumns.put(userfkName, depColumns);
			}
		}

		Map<String, List<ForeignKey>> oneToManyCandidates = new HashMap<String, List<ForeignKey>>();
		Iterator<Entry<String, Table>> iterator = dependentTables.entrySet().iterator();
		while (iterator.hasNext()) {
			Map.Entry<String, Table> entry = iterator.next();
			String fkName = (String) entry.getKey();
			Table fkTable = (Table) entry.getValue();
			List<Column> columns = dependentColumns.get(fkName);
			List<Column> refColumns = referencedColumns.get(fkName);

			String className = revengStrategy.tableToClassName(TableIdentifier
					.create(referencedTable));

			ForeignKey key = DatabaseCollector.createForeignKey(fkName, columns,
					className, refColumns, fkTable);
			key.setReferencedTable(referencedTable);

			addToMultiMap(oneToManyCandidates, className, key);
		}

		return oneToManyCandidates;
	}

	private void processPrimaryKey(Table table) throws JDBCBinderException,
			SQLException {
		ResultSet primaryKeyRs = null;
		try {
			primaryKeyRs = getMetaData().getPrimaryKeys(table.getCatalog(),
					table.getSchema(), table.getName());
			List<Object[]> columns = new ArrayList<Object[]>(10);
			PrimaryKey key = null;

			while (primaryKeyRs.next()) {
				/*
				 * String ownCatalog = primaryKeyRs.getString("TABLE_CAT");
				 * String ownSchema = primaryKeyRs.getString("TABLE_SCHEM");
				 * String ownTable = primaryKeyRs.getString("TABLE_NAME");
				 */

				String columnName = primaryKeyRs.getString("COLUMN_NAME");
				short seq = primaryKeyRs.getShort("KEY_SEQ");
				String name = primaryKeyRs.getString("PK_NAME");

				if (key == null) {
					key = DatabaseCollector.getFactory().createPrimaryKey();
					key.setName(name);
					key.setTable(table);
					if (table.getPrimaryKey() != null) {
						throw new JDBCBinderException(table
								+ " already has a primary key!"); 
					}
					table.setPrimaryKey(key);
				} else {
					if (!(name == key.getName()) && name != null
							&& !name.equals(key.getName())) {
						throw new JDBCBinderException(
								"Duplicate names found for primarykey. Existing name: "
										+ key.getName() + " JDBC name: " + name
										+ " on table " + table);
					}
				}

				columns.add(new Object[] { new Short(seq), columnName });

			}

			Collections.sort(columns, new Comparator<Object>() {
				public boolean equals(Object obj) {
					return super.equals(obj);
				}

				public int compare(Object o1, Object o2) {
					Short left = (Short) ((Object[]) o1)[0];
					Short right = (Short) ((Object[]) o2)[0];
					return left.compareTo(right);
				}

				public int hashCode() {
					return super.hashCode();
				}
			});

			Iterator<Object[]> cols = columns.iterator();
			while (cols.hasNext()) {
				Object[] element = cols.next();
				Column col = DatabaseCollector.getFactory().createColumn();
				col.setName((String) element[1]);
				Column existing = DatabaseCollector.getColumnByName(table, col); 
				if (existing != null) {
					col = existing;
				}
				DatabaseCollector.addIndexColumn(key,col);
			}
			if (key == null) {
				log
						.warn("The JDBC driver didn't report any primary key columns in "
								+ table.getName());
			} else {
				log.debug("primary key for " + table + " -> " + key);
			}
		} catch (SQLException se) {
			throw sec.convert(se,
					"Problems getting meta data for primary keys", null);
		} finally {
			if (primaryKeyRs != null) {
				try {
					primaryKeyRs.close();
				} catch (SQLException se) {
					log
							.warn(
									"Exception when closing resultset for reading primary key information",
									se);
				}
			}
		}
	}

	private void processTables(DatabaseCollector dbs, String catalog,
			String schema, ProgressListener progress) throws SQLException,
			JDBCBinderException {
		ResultSet tableRs = null;
		try {
			progress
					.startSubTask("Finding tables in " + catalog + "." + schema);
			tableRs = getMetaData().getTables(catalog, schema, null,
					new String[] { "TABLE", "VIEW" });
			while (tableRs.next()) {
				String tableName = tableRs.getString("TABLE_NAME");
				String schemaName = tableRs.getString("TABLE_SCHEM");
				String catalogName = tableRs.getString("TABLE_CAT");
				TableIdentifier ti = new TableIdentifier(catalogName,
						schemaName, tableName);
				if (revengStrategy.excludeTable(ti)) {
					log.debug("Table " + ti + " excluded by strategy");
					continue;
				}

				String tableType = tableRs.getString("TABLE_TYPE");
				if (("TABLE".equals(tableType) || "VIEW".equals(tableType) )) { 
					if (schemaName != null && schemaName.trim().length() == 0) {
						schemaName = null;
					}
					if (catalogName != null && catalogName.trim().length() == 0) {
						catalogName = null;
					}
					log.debug("Adding table " + tableName + " of type "
							+ tableType);
					progress.startSubTask("Found " + tableName);
					dbs.addTable(schemaName, catalogName, tableName);
				} else {
					log.debug("Ignoring table " + tableName + " of type "
							+ tableType);
				}
			}
		} catch (SQLException e) {
			String databaseStructure = getDatabaseStructure(catalog, schema);
			log.error(e.getMessage(), e);
			throw sec.convert(e,
					"Could not get list of tables from database. Probably a JDBC driver problem. "
							+ databaseStructure, null);
		} finally {
			try {
				if (tableRs != null)
					tableRs.close();
			} catch (Exception ignore) {
			}
		}
	}

	private void processBasicColumns(Table table, ProgressListener progress)
			throws SQLException, JDBCBinderException {
		ResultSet columnRs = null;
		String qualify = DatabaseCollector.qualify(table.getCatalog(), table.getSchema(),
				table.getName(), '.');

		try {
			log.debug("Finding columns for " + qualify);
			progress.startSubTask("Finding columns for " + qualify);
			columnRs = getMetaData().getColumns(table.getCatalog(),
					table.getSchema(), table.getName(), null);
			while (columnRs.next()) {

				int sqlType = columnRs.getInt("DATA_TYPE");
				String columnName = columnRs.getString("COLUMN_NAME");

				boolean isNullable = (DatabaseMetaData.columnNullable == columnRs
						.getInt("NULLABLE"));
				int size = columnRs.getInt("COLUMN_SIZE");
				int decimalDigits = columnRs.getInt("DECIMAL_DIGITS");

				Column column = DatabaseCollector.getFactory().createColumn();
				column.setName(columnName);
				Column existing = DatabaseCollector.getColumnByName(table, column); 
				if (existing != null) {
					throw new JDBCBinderException(column
							+ " already exists in " + qualify);
				}

				column.setSqlTypeCode(new Integer(sqlType));
				if (intBounds(size)) {
					if (columnHasLength(sqlType)) {
						column.setLength(size);
					}
					if (columnHasScaleAndPrecision(sqlType)) {
						column.setPrecision(size);
					}
				}
				if (intBounds(decimalDigits)) {
					if (columnHasScaleAndPrecision(sqlType)) {
						column.setScale(decimalDigits);
					}
				}

				column.setNullable(isNullable);

				DatabaseCollector.addColumn(table,column);

			}
		} catch (SQLException se) {
			throw sec.convert(se, "Error while reading column meta data for "
					+ qualify, null);
		} finally {
			if (columnRs != null) {
				try {
					columnRs.close();
				} catch (SQLException se) {
					log
							.warn(
									"Exception while closing resultset for column meta data",
									se);
				}
			}
		}

	}

	private DatabaseMetaData getMetaData() throws SQLException {
		if (metaData == null) {
			try {
				metaData = connection.getMetaData();
			} catch (SQLException e) {
				throw sec.convert(e, "Getting database metadata", null);
			}
		}
		return metaData;
	}

	private boolean columnHasScaleAndPrecision(int sqlType) {
		return (sqlType == Types.DECIMAL || sqlType == Types.NUMERIC
				|| sqlType == Types.REAL || sqlType == Types.FLOAT || sqlType == Types.DOUBLE);
	}

	private boolean columnHasLength(int sqlType) {
		return (sqlType == Types.CHAR || sqlType == Types.DATE
				|| sqlType == Types.LONGVARCHAR || sqlType == Types.TIME
				|| sqlType == Types.TIMESTAMP || sqlType == Types.VARCHAR);
	}

	private boolean intBounds(int size) {
		return size >= 0 && size != Integer.MAX_VALUE;
	}

	private String getDatabaseStructure(String catalog, String schema) {
		ResultSet schemaRs = null;
		ResultSet catalogRs = null;
		String nl = System.getProperty("line.separator");
		StringBuffer sb = new StringBuffer(nl);
		sb.append("Configured schema:").append(schema).append(nl);
		sb.append("Configured catalog:").append(catalog).append(nl);

		try {
			schemaRs = getMetaData().getSchemas();
			sb.append("Available schemas:").append(nl);
			while (schemaRs.next()) {
				sb.append("  ").append(schemaRs.getString("TABLE_SCHEM"))
						.append(nl);
			}
		} catch (SQLException e2) {
			log.warn("Could not get schemas", e2);
			sb.append("  <SQLException while getting schemas>").append(nl);
		} finally {
			try {
				schemaRs.close();
			} catch (Exception ignore) {
			}
		}

		try {
			catalogRs = getMetaData().getCatalogs();
			sb.append("Available catalogs:").append(nl);
			while (catalogRs.next()) {
				sb.append("  ").append(catalogRs.getString("TABLE_CAT"))
						.append(nl);
			}
		} catch (SQLException e2) {
			log.warn("Could not get catalogs", e2);
			sb.append("  <SQLException while getting catalogs>").append(nl);
		} finally {
			try {
				catalogRs.close();
			} catch (Exception ignore) {
			}
		}
		return sb.toString();
	}

	private void processIndices(Table table) {

		Map<String, Index> indexes = new HashMap<String, Index>(5); 
		Map<String, UniqueKey> uniquekeys = new HashMap<String, UniqueKey>(5);
		Map<Column, List<UniqueKey>> uniqueColumns = new HashMap<Column, List<UniqueKey>>(5); 
		ResultSet indexRs = null;

		try {

			indexRs = getMetaData().getIndexInfo(table.getCatalog(),
					table.getSchema(), table.getName(), false, true);

			while (indexRs.next()) {
				String indexName = indexRs.getString("INDEX_NAME");
				String columnName = indexRs.getString("COLUMN_NAME");
				boolean unique = !indexRs.getBoolean("NON_UNIQUE");

				if (columnName != null || indexName != null) { 
					if (unique) {
						UniqueKey key = uniquekeys.get(indexName);
						if (key == null) {
							key = DatabaseCollector.getFactory().createUniqueKey();
							key.setName(indexName);
							key.setTable(table);
							DatabaseCollector.addUniqueKey(table, key);
							
							uniquekeys.put(indexName, key);
						}

						if (indexes.containsKey(indexName)) {
							throw new JDBCBinderException(
									"UniqueKey exists also as Index! ");
						}
						Column column = DatabaseCollector.getColumn(table, columnName);

						DatabaseCollector.addIndexColumn(key, column);

						if (unique /*&& key.getColumnSpan() == 1*/) {
							List<UniqueKey> l = uniqueColumns.get(column);
							if (l == null) {
								l = new ArrayList<UniqueKey>();
								uniqueColumns.put(column, l);
							}
							l.add(key);
						}
					} else {
						Index index = indexes.get(indexName);
						if (index == null) {
							index = DatabaseCollector.getFactory().createIndex();
							index.setName(indexName);
							index.setTable(table);
							DatabaseCollector.addIndex(table, index);
							indexes.put(indexName, index);
						}

						if (uniquekeys.containsKey(indexName)) {
							throw new JDBCBinderException(
									"Index exists also as Unique! ");
						}
						Column column = DatabaseCollector.getColumn(table, columnName);
						DatabaseCollector.addIndexColumn(index,column);
					}

				} else {
					if (DatabaseMetaData.tableIndexStatistic != indexRs
							.getShort("TYPE")) {
						log
								.warn("Index was not statistical, but no column name was found in "
										+ indexName);
					}

				}
			}
		} catch (Throwable t) {
			log.warn("Exception while trying to get indexinfo on "
					+ DatabaseCollector.qualify(table.getCatalog(), table.getSchema(),
							table.getName(), '.') + "=" + t.getMessage());
		} finally {
			if (indexRs != null) {
				try {
					indexRs.close();
				} catch (SQLException se) {
					log
							.warn(
									"Exception while trying to close resultset for index meta data",
									se);
				}
			}
		}

		Iterator<Entry<Column, List<UniqueKey>>> iterator = uniqueColumns.entrySet().iterator();
		while (iterator.hasNext()) {
			Map.Entry<Column, List<UniqueKey>> entry = iterator.next();
			Column col = (Column) entry.getKey();
			Iterator<UniqueKey> keys = (entry.getValue()).iterator();
			while (keys.hasNext()) {
				keys.next();
				col.setUnique(true);
			}
		}
	}

	private void mergeMultiMap(Map<String, List<ForeignKey>> dest, Map<String, List<ForeignKey>> src) {
		Iterator<Entry<String, List<ForeignKey>>> items = src.entrySet().iterator();

		while (items.hasNext()) {
			Map.Entry<String, List<ForeignKey>> element = items.next();

			List<ForeignKey> existing = dest.get(element.getKey());
			if (existing == null) {
				dest.put(element.getKey(), element.getValue());
			} else {
				existing.addAll(element.getValue());
			}
		}

	}

	private boolean equalTable(Table table1, Table table2) {
		return table1.getName().equals(table2.getName())
				&& (equal(table1.getSchema(), table2.getSchema()) && (equal(
						table1.getCatalog(), table2.getCatalog())));
	}

	private boolean equal(String str, String str2) {
		if (str == str2)
			return true;
		if (str != null && str.equals(str2))
			return true;
		return false;
	}

	private void addToMultiMap(Map<String, List<ForeignKey>> multimap, String key, ForeignKey item) {
		List<ForeignKey> existing = multimap.get(key);
		if (existing == null) {
			existing = new ArrayList<ForeignKey>();
			multimap.put(key, existing);
		}
		existing.add(item);
	}

	public DatabaseCollector readDatabaseSchema(DatabaseCollector dbs,
			String catalog, String schema) throws SQLException,
			JDBCBinderException {
		return readDatabaseSchema(dbs, catalog, schema, new ProgressListener() {
			public void startSubTask(String name) {
			}

			public void setMonitor() {
			}
		});
	}

}
